﻿use persons
go
------------------4----------------------------------------------------------------
/*Find people with specified contact type*/
DECLARE  @CONTACT_TYPE NVARCHAR(50)
SET @CONTACT_TYPE=N'email'

SELECT 
[Person].[FirstName], 
[Person].[LastName]
FROM [dbo].[Contact], [dbo].[Person] 
WHERE [Person].[ID]=[Contact].[fk_personId] AND [Contact].[ContactType]=@CONTACT_TYPE

SELECT 
[Person].[FirstName], 
[Person].[LastName]
FROM [dbo].[Contact] INNER JOIN [dbo].[Person] 
ON [Person].[ID]=[Contact].[fk_personId] WHERE [Contact].[ContactType]=@CONTACT_TYPE

SELECT 
[Person].[FirstName],
[Person].[LastName]
FROM [dbo].[Person] WHERE [Person].[ID] IN(SELECT  
[Contact].[fk_personId] FROM [dbo].[Contact] WHERE [Contact].[ContactType]=@CONTACT_TYPE)
